#region Using
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using CY.GFive.Core.Business;
using CY.Utility.DBUtility;
#endregion Using

namespace CY.GFive.DALProviders.SqlServerProvider
{
	public class MeasureProvider : CY.GFive.Core.Providers.DALProvider.IMeasureProvider
	{
		#region Sql
		private static readonly string SqlSelectMeasure = "SELECT [Id], [MeasureName],[FacCode],[SchlCode],[Format],[Precision],[Num],[Factory],[FacDate],[Certification],[ProjectItem],[QualifiedNum],[FailureNum],[InUse],[AvailUse],[SchlCert],[ClassRoomCode],[DepartCode] FROM [Measure] WHERE  [Id]=@Id";

		private static readonly string SqlInsertMeasure = "INSERT INTO [Measure]([MeasureName],[FacCode],[SchlCode],[Format],[Precision],[Num],[Factory],[FacDate],[Certification],[ProjectItem],[QualifiedNum],[FailureNum],[InUse],[AvailUse],[SchlCert],[ClassRoomCode],[DepartCode]) values(@MeasureName,@FacCode,@SchlCode,@Format,@Precision,@Num,@Factory,@FacDate,@Certification,@ProjectItem,@QualifiedNum,@FailureNum,@InUse,@AvailUse,@SchlCert,@ClassRoomCode,@DepartCode);SELECT SCOPE_IDENTITY();";

		private static readonly string SqlUpdateMeasure = "UPDATE [Measure] SET [MeasureName] = @MeasureName,[FacCode] = @FacCode,[SchlCode] = @SchlCode,[Format] = @Format,[Precision] = @Precision,[Num] = @Num,[Factory] = @Factory,[FacDate] = @FacDate,[Certification] = @Certification,[ProjectItem] = @ProjectItem,[QualifiedNum] = @QualifiedNum,[FailureNum] = @FailureNum,[InUse] = @InUse,[AvailUse] = @AvailUse,[SchlCert] = @SchlCert,[ClassRoomCode] = @ClassRoomCode,[DepartCode] = @DepartCode WHERE  [Id]=@Id";

		private static readonly string SqlDeleteMeasure = "DELETE [Measure] WHERE  [Id]=@Id";

		private static readonly string SqlGetAllMeasure = "SELECT [Id], [MeasureName],[FacCode],[SchlCode],[Format],[Precision],[Num],[Factory],[FacDate],[Certification],[ProjectItem],[QualifiedNum],[FailureNum],[InUse],[AvailUse],[SchlCert],[ClassRoomCode],[DepartCode] FROM [Measure]";
		#endregion

		#region IMeasureProvider Members
		public Core.Business.Measure Select(int id)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Id", SqlDbType.Int, id);
			SqlDataReader reader = sql.ExecuteSqlReader(SqlSelectMeasure);

			if (reader != null && !reader.IsClosed && reader.Read())
			{
				Core.Business.Measure measure = new Core.Business.Measure();

				if (!reader.IsDBNull(0)) measure.Id = reader.GetInt32(0);
				if (!reader.IsDBNull(1)) measure.MeasureName = reader.GetString(1);
				if (!reader.IsDBNull(2)) measure.FacCode = reader.GetString(2);
				if (!reader.IsDBNull(3)) measure.SchlCode = reader.GetString(3);
				if (!reader.IsDBNull(4)) measure.Format = reader.GetString(4);
				if (!reader.IsDBNull(5)) measure.Precision = reader.GetString(5);
				if (!reader.IsDBNull(6)) measure.Num = reader.GetInt32(6);
				if (!reader.IsDBNull(7)) measure.Factory = reader.GetString(7);
				if (!reader.IsDBNull(8)) measure.FacDate = reader.GetDateTime(8);
				if (!reader.IsDBNull(9)) measure.Certification = reader.GetString(9);
				if (!reader.IsDBNull(10)) measure.ProjectItem = reader.GetString(10);
				if (!reader.IsDBNull(11)) measure.QualifiedNum = reader.GetInt32(11);
				if (!reader.IsDBNull(12)) measure.FailureNum = reader.GetInt32(12);
				if (!reader.IsDBNull(13)) measure.InUse = reader.GetInt32(13);
				if (!reader.IsDBNull(14)) measure.AvailUse = reader.GetInt32(14);
				if (!reader.IsDBNull(15)) measure.SchlCert = reader.GetString(15);
				if (!reader.IsDBNull(16)) measure.ClassRoomCode = reader.GetString(16);
				if (!reader.IsDBNull(17)) measure.DepartCode = reader.GetString(17);

				return measure;
			}
			else
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();

				return null;
			}
		}

		public void Insert(Core.Business.Measure measure)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@MeasureName", SqlDbType.NVarChar, measure.MeasureName);
			sql.AddParameter("@FacCode", SqlDbType.NVarChar, measure.FacCode);
			sql.AddParameter("@SchlCode", SqlDbType.NVarChar, measure.SchlCode);
			sql.AddParameter("@Format", SqlDbType.NVarChar, measure.Format);
			sql.AddParameter("@Precision", SqlDbType.NVarChar, measure.Precision);
			sql.AddParameter("@Num", SqlDbType.Int, measure.Num);
			sql.AddParameter("@Factory", SqlDbType.NVarChar, measure.Factory);
			sql.AddParameter("@FacDate", SqlDbType.DateTime, measure.FacDate);
			sql.AddParameter("@Certification", SqlDbType.NVarChar, measure.Certification);
			sql.AddParameter("@ProjectItem", SqlDbType.NVarChar, measure.ProjectItem);
			sql.AddParameter("@QualifiedNum", SqlDbType.Int, measure.QualifiedNum);
			sql.AddParameter("@FailureNum", SqlDbType.Int, measure.FailureNum);
			sql.AddParameter("@InUse", SqlDbType.Int, measure.InUse);
			sql.AddParameter("@AvailUse", SqlDbType.Int, measure.AvailUse);
			sql.AddParameter("@SchlCert", SqlDbType.NVarChar, measure.SchlCert);
			sql.AddParameter("@ClassRoomCode", SqlDbType.NVarChar, measure.ClassRoomCode);
			sql.AddParameter("@DepartCode", SqlDbType.NVarChar, measure.DepartCode);
			SqlDataReader reader = sql.ExecuteSqlReader(SqlInsertMeasure);

			if (reader != null && !reader.IsClosed && reader.Read())
			{
				if (!reader.IsDBNull(0)) measure.Id = int.Parse(reader.GetDecimal(0).ToString());

				reader.Close();
			}
			else
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

		public void Update(Core.Business.Measure measure)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@MeasureName", SqlDbType.NVarChar, measure.MeasureName);
			sql.AddParameter("@FacCode", SqlDbType.NVarChar, measure.FacCode);
			sql.AddParameter("@SchlCode", SqlDbType.NVarChar, measure.SchlCode);
			sql.AddParameter("@Format", SqlDbType.NVarChar, measure.Format);
			sql.AddParameter("@Precision", SqlDbType.NVarChar, measure.Precision);
			sql.AddParameter("@Num", SqlDbType.Int, measure.Num);
			sql.AddParameter("@Factory", SqlDbType.NVarChar, measure.Factory);
			sql.AddParameter("@FacDate", SqlDbType.DateTime, measure.FacDate);
			sql.AddParameter("@Certification", SqlDbType.NVarChar, measure.Certification);
			sql.AddParameter("@ProjectItem", SqlDbType.NVarChar, measure.ProjectItem);
			sql.AddParameter("@QualifiedNum", SqlDbType.Int, measure.QualifiedNum);
			sql.AddParameter("@FailureNum", SqlDbType.Int, measure.FailureNum);
			sql.AddParameter("@InUse", SqlDbType.Int, measure.InUse);
			sql.AddParameter("@AvailUse", SqlDbType.Int, measure.AvailUse);
			sql.AddParameter("@SchlCert", SqlDbType.NVarChar, measure.SchlCert);
			sql.AddParameter("@ClassRoomCode", SqlDbType.NVarChar, measure.ClassRoomCode);
			sql.AddParameter("@DepartCode", SqlDbType.NVarChar, measure.DepartCode);
			sql.AddParameter("@Id", SqlDbType.Int, measure.Id);
			sql.ExecuteSql(SqlUpdateMeasure);
		}

		public void Delete(Core.Business.Measure measure)
		{
			SqlServerUtility sql = new SqlServerUtility();

			sql.AddParameter("@Id", SqlDbType.Int, measure.Id);
			sql.ExecuteSql(SqlDeleteMeasure);
		}

		public IList<Core.Business.Measure> GetAllMeasure()
		{
			IList<Core.Business.Measure> measurelist = new List<Core.Business.Measure>();
			SqlServerUtility sql = new SqlServerUtility();

			SqlDataReader reader = sql.ExecuteSqlReader(SqlGetAllMeasure);

			if(reader != null)
			{
				while(reader.Read())
				{
					Core.Business.Measure measure = new Core.Business.Measure();

					if (!reader.IsDBNull(0)) measure.Id = reader.GetInt32(0);
					if (!reader.IsDBNull(1)) measure.MeasureName = reader.GetString(1);
					if (!reader.IsDBNull(2)) measure.FacCode = reader.GetString(2);
					if (!reader.IsDBNull(3)) measure.SchlCode = reader.GetString(3);
					if (!reader.IsDBNull(4)) measure.Format = reader.GetString(4);
					if (!reader.IsDBNull(5)) measure.Precision = reader.GetString(5);
					if (!reader.IsDBNull(6)) measure.Num = reader.GetInt32(6);
					if (!reader.IsDBNull(7)) measure.Factory = reader.GetString(7);
					if (!reader.IsDBNull(8)) measure.FacDate = reader.GetDateTime(8);
					if (!reader.IsDBNull(9)) measure.Certification = reader.GetString(9);
					if (!reader.IsDBNull(10)) measure.ProjectItem = reader.GetString(10);
					if (!reader.IsDBNull(11)) measure.QualifiedNum = reader.GetInt32(11);
					if (!reader.IsDBNull(12)) measure.FailureNum = reader.GetInt32(12);
					if (!reader.IsDBNull(13)) measure.InUse = reader.GetInt32(13);
					if (!reader.IsDBNull(14)) measure.AvailUse = reader.GetInt32(14);
					if (!reader.IsDBNull(15)) measure.SchlCert = reader.GetString(15);
					if (!reader.IsDBNull(16)) measure.ClassRoomCode = reader.GetString(16);
					if (!reader.IsDBNull(17)) measure.DepartCode = reader.GetString(17);

					measure.MarkOld();
					measurelist.Add(measure);
				}
				reader.Close();
			}
			return measurelist;
		}
		#endregion
	}
}
